/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cs1555prj;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.tree.DefaultMutableTreeNode;

/**
 *
 * @author sddys
 */
public class MyDataBase {

    private Connection connection;
    private final String username = "qid4";
    private final String password = "3516175";
    private final String url = "jdbc:oracle:thin:@db10.cs.pitt.edu:1521:dbclass";
    private boolean bConnection;

    public MyDataBase() {
        bConnection = false;
    }

    /**
     * create connection to database
     */
    public boolean openConnection() {
        try {
            //Register the oracle driver.  This needs the oracle files provided
            //in the oracle.zip file, unzipped into the local directory and
            //the class path set to include the local directory
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            connection = DriverManager.getConnection(url, username, password);
            //create a connection to DB
        } catch (Exception Ex) //What to do with any exceptions
        {
            System.out.println("Error connecting to database.  Machine Error: "
                    + Ex.toString());
            bConnection = false;
            return false;
        }
        bConnection = true;
        return true;
    }

    /**get category tree*/
    DefaultMutableTreeNode getCatelogs() {
        DefaultMutableTreeNode root = new DefaultMutableTreeNode("root");
        HashMap<String, DefaultMutableTreeNode> map = new HashMap<String, DefaultMutableTreeNode>();
        Statement statement;
        ResultSet resultSet;
        String query = "";
        try {
            statement = connection.createStatement();//create an instance
            query = "select name, parent_catalog from catalog order by name";
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                DefaultMutableTreeNode node;
                String name = resultSet.getString("name");
                String parent = resultSet.getString("parent_catalog");
                if (parent.equals(name)) {//that is a root node
                    node = new DefaultMutableTreeNode(name);//create the node
                    root.add(node);//add node to root
                    map.put(name, node);//put it into map
                } else {//it's not a root level node
                    DefaultMutableTreeNode parentNode = map.get(parent);
                    node = map.get(name);//find out
                    if (node == null) {//if this node doesn't exist, create it and put it in map
                        node = new DefaultMutableTreeNode(name);
                        map.put(name, node);
                    }
                    if (parentNode == null) {//if parent node is not in the hashmap
                        parentNode = new DefaultMutableTreeNode(parent);//create the node
                        //add parentNode to hashmap
                        map.put(parent, parentNode);
                    }
                    parentNode.add(node);
                }

            }
        } catch (Exception Ex) {
            System.out.println("Error running the sample query " + query + ".  Machine Error: "
                    + Ex.toString());
            map.clear();
            return null;
        }
        map.clear();
        return root;
    }

    /**close connection to database*/
    public boolean closeConnection() {
        if (!bConnection) {
            return true;
        }
        try {
            connection.close();
        } catch (SQLException ex) {
            //Logger.getLogger(MyDataBase.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Error closing the database.  Machine Error: "
                    + ex.toString());
            return false;
        }
        return true;
    }

    /**
     * Get auction entry using id
     */
    public ResultSet getAuction(int id) {
        ResultSet rs = null;
        Statement statement = null;
        return rs;
    }

    /**get user name by login*/
    public String getNameByLogin(String login, boolean bCustomer) {
        String userName = "";
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        ResultSet rs = null;
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = String.format("select * from customer where login = \'%s\' ", login);
            //System.out.println(query);
            rs = statement.executeQuery(query);
            if (!rs.next()) {//no such user
                return null;
            } else {
                return rs.getString("name");
            }
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
        }
        return userName;
    }

    /**
     * get underauction product info from db whose catalog belong to str and orde by order
     * @param cat categories specified by user
     * @param order it will order by order
     */
    public ResultSet getProductsByCategories(Object cat[], String order) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select distinct(P.auction_id), P.name product1, P.description desc1, S.name seller1, \n"
                    + "to_char(P.start_date,'YYYY/MM/DD-HH:MI') date1, P.number_of_days days1, "
                    + "(case when HBID.max is null then (select min_price from product where auction_id = P.auction_id) "
                    + " else HBID.max end) max1, "
                    + " (case when HBID.name is null then 'No Bidder' else HBID.name end) as bidder1 \n"
                    + "from product P left outer join \n"
                    + "(select st1.auction_id, sq2.max, sC1.name \n"
                    + "from Bidlog st1, (select auction_id, max(sq1.amount) max \n"
                    + "from Bidlog sq1 \n"
                    + "group by sq1.auction_id) sq2, Customer sC1 \n"
                    + "where st1.auction_id = sq2.auction_id and \n"
                    + "	st1.amount = sq2.max and \n"
                    + "	st1.bidder = sC1.login) HBID \n"
                    + "on P.auction_id = HBID.auction_id, Customer S, BelongsTo B \n"
                    + "where P.auction_id = B.auction_id and P.status = \'underauction\' and\n"
                    + "	P.seller = S.login ";
            if (cat.length != 0) {
                query += "and (";
                for (int i = 0; i < cat.length - 1; i++) {
                    query += "B.catalog = \'" + cat[i].toString() + "\' or ";
                }
                query += "B.catalog = \'" + cat[cat.length - 1].toString() + "\') \n";
            }
            query += "order by " + order;
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**
     * get all product info from customer
     * @param customer customer login
     */
    public ResultSet getAllProductsByCustomer(String customer) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select distinct(P.auction_id), P.name product1, P.description desc1, S.name seller1, \n"
                    + "to_char(P.start_date,'YYYY/MM/DD-HH:MI') date1, P.number_of_days days1, P.status status1, P.buyer buyer1, "
                    + "(case when HBID.max is null then (select min_price from product where auction_id = P.auction_id) "
                    + " else HBID.max end) max1, "
                    + " (case when HBID.name is null then 'No Bidder' else HBID.name end) as bidder1 \n"
                    + "from product P left outer join \n"
                    + "(select st1.auction_id, sq2.max, sC1.name \n"
                    + "from Bidlog st1, (select auction_id, max(sq1.amount) max \n"
                    + "from Bidlog sq1 \n"
                    + "group by sq1.auction_id) sq2, Customer sC1 \n"
                    + "where st1.auction_id = sq2.auction_id and \n"
                    + "	st1.amount = sq2.max and \n"
                    + "	st1.bidder = sC1.login) HBID \n"
                    + "on P.auction_id = HBID.auction_id, Customer S, BelongsTo B \n"
                    + "where P.auction_id = B.auction_id and \n"
                    + (customer.equals("") ? "" : " P.seller = \'" + customer + "\' and\n")
                    + "	P.seller = S.login ";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**
     * Get all product by keywords
     * @key1 keyword1
     * @key2 keyword2
     */
    public ResultSet getAllProductsByKeywords(String key1, String key2) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select * from product where \n";
            ;
            if (!key1.equals("")) {
                query += " lower(description) LIKE lower(\'%" + key1 + "%\') ";
            }
            if (!key1.equals("") && !key2.equals("")) {
                query += " and ";
            }
            if (!key2.equals("")) {
                query += " lower(description) LIKE lower(\'%" + key2 + "%\') ";
            }
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**
     * check if it's a valid customer
     */
    public boolean isValidCustomer(String login, String pwd) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return false;
        }
        String query = "";
        ResultSet rs = null;
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = String.format("select * from customer where login = \'%s\' and password = \'%s\' ", login, pwd);

            //System.out.println(query);
            rs = statement.executeQuery(query);
            if (!rs.next()) {//no such user
                return false;
            }
            return true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return false;
        }
    }

    /**
     * Check if it's a valid user
     */
    public boolean isValidAdmin(String login, String pwd) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return false;
        }
        String query = "";
        ResultSet rs = null;
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = String.format("select * from administrator where login = \'%s\' and password = \'%s\' ", login, pwd);
            //System.out.println(query);
            rs = statement.executeQuery(query);
            if (!rs.next()) {//no such administrator
                return false;
            }
            return true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return false;
        }
    }

    /**
     * get product's bidding information based on auction_id
     * @param auction_id the auction_id on product table
     */
    public ResultSet getBiddingInfo(int auctionId) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select distinct(P.auction_id) auctionId1, P.name product1, P.description desc1, S.name seller1, \n"
                    + "to_char(P.start_date,'YYYY/MM/DD-HH:MI') date1, P.number_of_days days1, "
                    + "(case when HBID.max is null then (select min_price from product where auction_id = P.auction_id) "
                    + " else HBID.max end) max1, "
                    + " (case when HBID.name is null then 'No Bidder' else HBID.name end) as bidder1 \n"
                    + "from product P left outer join \n"
                    + "(select st1.auction_id, sq2.max, sC1.name \n"
                    + "from Bidlog st1, (select auction_id, max(sq1.amount) max \n"
                    + "from Bidlog sq1 \n"
                    + "group by sq1.auction_id) sq2, Customer sC1 \n"
                    + "where st1.auction_id = sq2.auction_id and \n"
                    + "	st1.amount = sq2.max and \n"
                    + "	st1.bidder = sC1.login) HBID \n"
                    + "on P.auction_id = HBID.auction_id, Customer S, BelongsTo B \n"
                    + "where P.auction_id = B.auction_id and \n"
                    + "	P.seller = S.login and P.auction_id = ";
            query += "\' " + auctionId + " \'";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**get top two highest bid on a product*/
    public ResultSet getSecondHighestBid(int auctionId) {
        ResultSet rs = null;
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = " select t1.bidsn, t1.bidder, t1.amount \n"
                    + "from (select bidsn, auction_id, rownum r, amount, bidder \n"
                    + "from (select * from bidlog \n"
                    + "where auction_id = "
                    + auctionId + " \n"
                    + "order by amount desc)) t1 "
                    + "where t1.r <= 2 ";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**put a new bid on a product*/
    public boolean putNewBid(int auctionId, int newPrice, String bidder) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return false;
        }
        Statement statement = null;
        String query = "";
        ResultSet rs = null;
        try {
            //set serializeable isolation
            connection.setAutoCommit(false); //the default is true and every statement executed is considered a transaction.
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//set serializable
            statement = connection.createStatement();
            //check to make sure no body bid a higher price then our new price
            query = "select max(amount) max1 from bidlog where auction_id = " + auctionId;
            rs = statement.executeQuery(query);
            //if some one bid a higher price than use already
            if (rs.next() && newPrice <= rs.getInt("max1")) {
                System.out.println("It seems like someone bid a higher price than us already!");
                return false;
            }
            //insert the new bid
            statement = connection.createStatement();
            query = "insert into bidlog values ( (select max(bidsn)+1 id1 from bidlog), "
                    + auctionId + ", \'"
                    + bidder + "\', (select c_date from ourdate), " + newPrice + " )";
            int rowcount = statement.executeUpdate(query);
            if (rowcount != 1) {
                throw new Exception("putNewBid: insert return row count not 1!\n");
            }
            connection.commit();    //commit the statement
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//set isolation level back
            connection.setAutoCommit(true);//set auto commit to true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return false;
        }
        return true;
    }

    /**
     * create a new customer or admin
     */
    public boolean createNewUser(String name1, String addr1, String email1, String login1, String pwd1, boolean isCustomer) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return false;
        }
        String query = "";
        try {
            //set serializeable isolation
            connection.setAutoCommit(false); //the default is true and every statement executed is considered a transaction.
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//set serializable
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "insert into " + (isCustomer ? "customer values ( " : "administrator values( ")
                    + "\'" + login1 + "\',"
                    + "\'" + pwd1 + "\', "
                    + "\'" + name1 + "\', "
                    + "\'" + addr1 + "\', "
                    + "\'" + email1 + "\' ) ";
            //System.out.println(query);
            int rowcount = statement.executeUpdate(query);
            if (rowcount != 1) {
                throw new Exception("create new user: insert return row count not 1!\n");
            }
            connection.commit();    //commit the statement
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//set isolation level back
            connection.setAutoCommit(true);//set auto commit to true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return false;
        }
        return true;
    }

    /**
     * @return 1 if this user login already exist
     * @return 0 if this user login not exist
     * @return -1 if it fail somehow
     */
    public int isLoginExist(String login1, boolean isCustomer) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return -1;
        }
        String query = "";
        try {
            //set serializeable isolation
            connection.setAutoCommit(false); //the default is true and every statement executed is considered a transaction.
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//set serializable
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select * from " + (isCustomer ? "customer " : "administrator ")
                    + "where login = \'" + login1 + "\' ";
            //System.out.println(query);
            ResultSet rs = statement.executeQuery(query);
            boolean rt = rs.next();
            connection.commit();    //commit the statement
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//set isolation level back
            connection.setAutoCommit(true);//set auto commit to true;
            return (rt ? 1 : 0);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return -1;
        }
    }

    /**get current system time*/
    public java.util.Date getSystemTime() {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select to_char(c_date,\'yyyymmddhhmiss\') date1 from ourdate";
            //System.out.println(query);
            ResultSet rs = statement.executeQuery(query);
            if (rs.next()) {
                String str = rs.getString("date1");
                SimpleDateFormat Formatter = new SimpleDateFormat("yyyyMMddhhmmss");
                java.util.Date utildate = (java.util.Date) Formatter.parse(str);
                return utildate;
            }
            return null;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**set current system time*/
    public void setSystemTime(Calendar newTime) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            SimpleDateFormat dateformat = new SimpleDateFormat("MM/dd/yyyy hh:mm:ss");
            String date1 = (dateformat.format(newTime.getTime()));
            query = "update ourdate set c_date = to_date(\'" + date1
                    + "\', \'MM/dd/yyyy hh:mi:ss\') ";
            //System.out.println(query);
            int rowcount = statement.executeUpdate(query);
            if (rowcount != 1) {
                throw new Exception("update new date return row count not 1!\n");
            }
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
        }
    }

    /**
     * Sell the product
     */
    public void sellProduct(int auctionID, String price1, String bidder1) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return;
        }
        String query = "";
        try {
            //set serializeable isolation
            connection.setAutoCommit(false); //the default is true and every statement executed is considered a transaction.
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//set serializable
            Statement statement = null;
            statement = connection.createStatement();//create an instancel
            query = "update product set status = \'sold\', buyer = \'" + bidder1 + "\', "
                    + "sell_date = (select c_date from ourdate), amount = " + price1
                    + " where auction_id = " + auctionID;
            System.out.println(query);
            int rowcount = statement.executeUpdate(query);
            if (rowcount != 1) {
                throw new Exception("update new date return row count not 1!\n");
            }
            connection.commit();    //commit the statement
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//set isolation level back
            connection.setAutoCommit(true);//set auto commit to true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
        }
    }

    /**
     * withdraw a product
     */
    public void withdrawProduct(int auctionID) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return;
        }
        String query = "";
        try {
            //set serializeable isolation
            connection.setAutoCommit(false); //the default is true and every statement executed is considered a transaction.
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//set serializable
            Statement statement = null;
            statement = connection.createStatement();//create an instancel
            query = "update product set status = \'withdrawn\'"
                    + " where auction_id = " + auctionID;
            System.out.println(query);
            int rowcount = statement.executeUpdate(query);
            if (rowcount != 1) {
                throw new Exception("update new date return row count not 1!\n");
            }
            connection.commit();    //commit the statement
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//set isolation level back
            connection.setAutoCommit(true);//set auto commit to true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
        }
    }

    /**get all customer logins, for admin only*/
    public ResultSet getAllCustomerLogin() {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select login from customer";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**get top k product count for previous x months*/
    public ResultSet getStatForLeaf(int k, int x) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select t2.name, t2.itemsold from"
                    + "(select t1.name, Product_Count(" + x + ",t1.name) itemsold \n"
                    + "from (select name from catalog \n"
                    + "minus \n"
                    + "select distinct(parent_catalog) name from catalog) t1 \n"
                    + "order by itemsold desc) t2 \n"
                    + "where rownum <=" + k + " ";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**get top k product count for previous x months*/
    public ResultSet getStatForRoot(int k, int x) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select t2.name, t2.itemsold from"
                    + "(select t1.name, Product_Count(" + x + ",t1.name) itemsold \n"
                    + "from (select name from catalog \n"
                    + "minus \n"
                    + "select distinct(parent_catalog) name from catalog) t1 \n"
                    + "order by itemsold desc) t2 \n"
                    + "where rownum <=" + k + " ";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**get top k product count for previous x months*/
    public ResultSet getStatForBidder(int k, int x) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select * from \n"
                    + " (select t1.bidder, Bid_count(" + x + ", t1.bidder) count \n"
                    + " from (select distinct(bidder) \n"
                    + " from bidlog) t1 \n"
                    + " order by count desc) \n"
                    + " where rownum <= " + k + " ";
            //System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**get top k product count for previous x months*/
    public ResultSet getStatForBuyer(int k, int x) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return null;
        }
        String query = "";
        try {
            Statement statement = null;
            statement = connection.createStatement();//create an instance
            query = "select * from \n"
                    + " (select t1.buyer, Buying_Amount(" + x + ", t1.buyer) amount \n"
                    + " from (select distinct(buyer) \n"
                    + " from product where status = \'sold\' ) t1 \n"
                    + " order by amount desc) \n"
                    + " where rownum <= " + k + " ";
            System.out.println(query);
            return statement.executeQuery(query);
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
            return null;
        }
    }

    /**put a product for auction*/
    public void putProductOnAuction(String name1, String description1, String seller1, int price1, int day1, ArrayList<String> list) {
        if (!bConnection)//return null if no connection
        {
            System.out.println("Connection fail!\n");
            return;
        }
        String query = "";
        try {
            //set serializeable isolation
            int auctionID = -1;
            connection.setAutoCommit(false); //the default is true and every statement executed is considered a transaction.
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);//set serializable
            //Statement statement = null;
            //statement = connection.createStatement();//create an instancel
            query = "{ call put_Product(?, ?, ?, ?, ?, ?) }";//
            CallableStatement cs = connection.prepareCall(query);
            /*(name1 in varchar2,description1 in varchar2,seller1 in varchar2,min_price1 in int,number_of_days1 in int, auctionID in out int)
            query = "call put_product(\'"
            + name1 +"\', \'"
            + description1+"\', \'"
            + seller1+"\', "
            + min1+", "
            + day1+","
            + ") ";*/
            //System.out.println(query);
            cs.setString(1, name1);
            cs.setString(2, description1);
            cs.setString(3, seller1);
            cs.setInt(4, price1);
            cs.setInt(5, day1);
            cs.registerOutParameter(6, java.sql.Types.INTEGER);
            cs.execute();   //execute query
            auctionID = cs.getInt(6);   //get auction id back
            //update all categories
            for (String cat : list) {//insert each category
                Statement statement = null;
                statement = connection.createStatement();//create an instancel
                query = "insert into belongsto values ("+auctionID
                        +", \'"+cat + "\' ) ";
                statement.executeQuery(query);
            }
            connection.commit();    //commit the statement
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//set isolation level back
            connection.setAutoCommit(true);//set auto commit to true;
        } catch (Exception Ex) {
            System.out.println("Error running the sample query:\n " + query + "  Machine Error: "
                    + Ex.toString());
        }
    }
}
